156

Beginner’s Guide to Code Algorithms

156

STEP 7 continued

  thisWsGraphs.Shapes(thisWsGraphs.Shapes.Count).Select

  Selection.ShapeRange.ScaleWidth 0.35, msoFalse, msoScaleFromTopLeft

  Selection.ShapeRange.ScaleHeight 0.3, msoFalse, msoScaleFromTopLeft

  Selection.ShapeRange.ZOrder msoBringToFront

  StateExists =​ ShapeColor(Left(thisWs.Cells(1, 2 * mm), 2))

  thisWsGraphs.Shapes(NameOfChart).Select

  ActiveChart.FullSeriesCollection(1).Select

STEP 8

Add the other states to the second chart. Note the use of the variable

SpecificStateColumn to determine if this is a “Top x” graph or a graph of specific

states requested by the user.

 

  If ErrorInput =​ “N” Then

  If SpecificStateColumn =​ ““ Then

    jj =​ 1

      For m =​ 2 To TopHowMany

        mm =​ thisWb.Sheets(“States”).Cells(1 +​ m, 7) -​ 1

        jj =​ jj +​ 1

        thisWsGraphs.Shapes(NameOfChart).Select

        ActiveChart.SeriesCollection.Add Source:=​Range(thisWs.Cells(startrow(1),

2 * mm), thisWs.Cells(j, 2 * mm))

        Set rRange =​ Range(thisWs.Cells(1, 2 * mm), thisWs.Cells(1, 2 * mm))

        ActiveChart.SeriesCollection(jj).Name =​ “=​‘Any State’!” & rRange.

Address(True, True, xlR1C1)

        Set rRange =​ Range(thisWs.Cells(startrow(1), 1), thisWs.Cells(j, 1))

        ActiveChart.SeriesCollection(jj).XValues =​ “=​‘Any State’!” & rRange.

Address(True, True, xlR1C1)

        StateExists =​ ShapeColor(Left(thisWs.Cells(1, 2 * mm), 2))

      Next

    Else

      jj =​ 1

      For m =​ 1 To UBound(SpecificState)

        If SpecificState(m) =​ ““ Then

          Exit For

        End If

          SpecificStateColumn =​ ““

          For n =​ 1 To NumberOfStates

        a =​ InStr(1, SpecificState(m), thisWb.Sheets(“States”).Cells(1 +​ n, 1), 1)

          If a > 0 Then

            SpecificState(m) =​ Mid(SpecificState(m), 1, 2)

            SpecificStateColumn =​ n

          End If

        Next

        If SpecificStateColumn <> ““ Then

          mm =​ SpecificStateColumn

          jj =​ jj +​ 1

          thisWsGraphs.Shapes(NameOfChart).Select

          ActiveChart.SeriesCollection.Add Source:=​Range(thisWs.

Cells(startrow(1), 2 * mm), thisWs.Cells(j, 2 * mm))